﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;

namespace StreetSmartAdmin
{


    public partial class EACancelled : System.Web.UI.Page
    {
        //string strsql = "select DateConverted as 'Date Converted', OperatorSSE as 'Operator SSE', SourceCode as 'Source Code', VendorID as 'Vendor ID', RESI_SME as 'RESI/SME', ElectricityConversion as 'Electricity Conversion?', GasConversion as 'Gas Conversion?', ElecTotalAnnualSpend as 'Annual Electricity Spend', GasTotalAnnualSpend as 'Annual Gas Spend', MoverSwicher as 'Mover/Swicher', MoveInDate as 'Move In Date', CompanyName as 'Company Name', CompanyABN as 'Company ABN', FirstAccountHolderTitle as 'First Account Holder Title',  FirstAccountHolderFirstName as 'First Account Holder First Name', FirstAccountHolderSurname as 'First Account Holder Surname', FirstAccountHolderDOB as 'First Account Holder DOB', ID_Number as 'ID Number', ID_Expiry as 'ID Expiry', ID_Type as 'ID Type', SecondAccountHolderTitle as 'Second Account Holder Title', SecondAccountHolderFirstName as 'Second Account Holder First Name', SecondAccountHolderSurname as 'Second Account Holder Surname', SecondAccountHolderDOB as 'Second Account Holder DOB', ContactTel as 'Contact Tel', SecondTel as 'SecondTel', Email, SupplyAddress as 'Supply Address', SupplyAddressSuburb as 'Supply Address Suburb', SupplyAddressState as 'Supply Address State', SupplyAddressPostcode as 'Supply Address Postcode', TenantOwner as 'Tenant/Owner', Elec, Gas, ProductType as 'Product Type', NoOfPeopleInHouse as 'No Of People In House', ConcessionCardType as 'Concession Card Type', ConcessionCardEntitlement as 'Concession Card Entitlement', ConcessionCardNo as 'Concession Card No', ConcessionCardExp as 'Concession Card Exp', NameOnConcessionCard as 'Name On Concession Card', NMI, MIRN, AnyHazards as 'Any Hazards?', AnyAccessRequirements as 'Any Access Requirements?', SpecialInstructionsForAccess as 'Special Instructions For Access?', AdvisedMainSwitchNeedsTurningOff as 'Advised Main Switch Needs Turning Off?', SafetyCertificateRequired as 'Safety Certificate Required?', ElecStatus as 'Elec Status', GasStatus as 'Gas Status', Reason, TRUenergyComments as 'TRUenergy Comments', ExistingTRUEnergyCustomer as 'Existing TRUEnergy Customer?' from tblUser ";
        string strsql = "select * from tblCancellation ";
        protected string connectstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ConnectionString;
        public static String date = DateTime.Now.Date.ToString("yyyy-MM-dd");
        public static String time = DateTime.Now.ToString("yyyyMMdd_HHmmss");
        public static String pre = "SSE_TO_EA_Cancs_";
        public static String Filename = pre + time + ".txt";

        protected void Page_Load()
        {
            ((Label)Header.FindControl("lblUser")).Text = (String)Session["UserName"];
            String date = DateTime.Now.Date.ToString("yyyy-MM-dd ");
            TextBox1.Text = date;
            System.Configuration.ConnectionStringSettings connString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"];


        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            String querySql = strsql + "where CANCELLATION_DATE = '" + TextBox1.Text + "' order by CANCELLATION_DATE;";
            SqlConnection SqlConnection = new SqlConnection();
            SqlConnection.ConnectionString = connectstring;
            SqlCommand command = new SqlCommand(querySql, SqlConnection);
            SqlConnection.Open();
            DataTable dt = GetDataTable(command);
            StringWriter sw = new StringWriter();
            Export_Excel(dt, sw);
            Response.AddHeader("Content-Disposition", "attachment; filename=Converted.xls");
            Response.ContentType = "application/ms-excel";
            Response.Write(sw);
            Response.End();
            SqlConnection.Close();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            //String querySql = strsql + "where DateConverted >= '" + TextBox2.Text + "'and DateConverted <= '" + TextBox3.Text + "' order by DateConverted;";

            String querySql = strsql + "where CANCELLATION_DATE ='" + date + "' order by CANCELLATION_DATE;";
            SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
            SqlConnection.ConnectionString = connectstring;
            SqlCommand command = new SqlCommand(querySql, SqlConnection);
            SqlConnection.Open();
            DataTable dt = GetDataTable(command);
            //ExportOnline(dt);
            ExportLocal(dt);
            SqlConnection.Close();
        }

        public string ChangeDateFormit(string olddate)
        {
            DateTime date = Convert.ToDateTime(olddate);
            string year = date.Year.ToString();
            string month = "";
            string day = "";
            if (date.Month.ToString().Length < 2)
                month = "0" + date.Month.ToString();
            else
                month = date.Month.ToString();
            if (date.Day.ToString().Length < 2)
                day = "0" + date.Day.ToString();
            else
                day = date.Day.ToString();
            string newdate = day + "/" + month + "/" + year;
            return newdate;
        }


        public void ExportLocal(DataTable dt)
        {
            string path = Server.MapPath(".");
            string filepath = path + "\\" + Filename;
            StreamWriter w = File.CreateText(@filepath);
            try
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    w.Write(dt.Columns[i]);
                    w.Write('|');
                }
                w.Write('\n');
                object[] values = new object[dt.Columns.Count];
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    values = dr.ItemArray;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (i == 1)
                        {
                            if (values[i].ToString() != "")
                                w.Write(ChangeDateFormit(values[i].ToString()));
                            else
                                w.Write(values[i]);
                        }
                        else
                            w.Write(values[i]);
                        w.Write('|');
                    }
                    w.Write('\n');
                }
                w.Flush();
                w.Close();
            }
            catch
            {
                w.Close();
            }
        }

        public void ExportOnline(DataTable dt)
        {
            StringWriter sw = new StringWriter();
            Export_TXT(dt, sw);
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Filename);
            Response.ContentType = "application/txt";
            Response.Write(sw);
            Response.End();
        }

        public static DataTable GetDataTable(System.Data.SqlClient.SqlCommand cmd)
        {

            // create a new data adapter based on the specified query.
            SqlDataAdapter da = new SqlDataAdapter();
            //set the SelectCommand of the adapter
            da.SelectCommand = cmd;
            // create a new DataTable
            DataTable dtGet = new DataTable();
            //fill the DataTable
            da.Fill(dtGet);
            //return the DataTable
            return dtGet;
        }

        public void Export_Excel(DataTable dt, StringWriter w)
        {
            try
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    w.Write(dt.Columns[i]);
                    w.Write('\t');
                }
                w.Write('\n');
                object[] values = new object[dt.Columns.Count];
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    values = dr.ItemArray;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        w.Write(values[i]);
                        w.Write('\t');
                    }
                    w.Write('\n');
                }
                w.Flush();
                w.Close();
            }
            catch
            {
                w.Close();
            }
        }

        public void Export_TXT(DataTable dt, StringWriter w)
        {
            try
            {
                for (int i = 2; i < dt.Columns.Count; i++)
                {
                    w.Write(dt.Columns[i]);
                    w.Write('|');
                }
                w.Write('\n');
                object[] values = new object[dt.Columns.Count];
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    values = dr.ItemArray;
                    for (int i = 2; i < dt.Columns.Count; i++)
                    {
                        w.Write(values[i]);
                        w.Write('|');
                    }
                    w.Write('\n');
                }
                w.Flush();
                w.Close();
            }
            catch
            {
                w.Close();
            }
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            GridView1.DataSourceID = "SqlDataSource1";
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            GridView1.AllowPaging = false;
            GridView1.DataBind();
            Response.Clear();
            Response.AppendHeader("Content-Disposition", "attachment;filename= " + Server.UrlEncode("Export.xls"));
            Response.ContentType = "application/ms-excel";
            this.EnableViewState = false;
            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("EN", true);
            System.IO.StringWriter stringWrite = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
            GridView1.RenderControl(htmlWrite);
            Response.Write(stringWrite.ToString());
            Response.End();
            GridView1.AllowPaging = true;
        }

        override public void VerifyRenderingInServerForm(System.Web.UI.Control control)
        { }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            GridView1.PageSize = Convert.ToInt32(DropDownList1.SelectedValue);
        }


        private void MessageBox(string message)
        {
            if (!string.IsNullOrEmpty(message))
            {
                this.ClientScript.RegisterClientScriptBlock(this.GetType(), "alt1", "alert('" + message + "');", true);
            }
        }





        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (RadioButtonList1.SelectedValue == "all")
            {
                GridView2.DataBind();
                MultiView1.ActiveViewIndex = 2;
            }
            else if (RadioButtonList1.SelectedValue == "add")
            {
                GridView2.DataBind();
                MultiView1.ActiveViewIndex = 1;

            }
            else if (RadioButtonList1.SelectedValue == "edit")
            {
                GridView2.DataBind();
                MultiView1.ActiveViewIndex = 0;

            }
        }
}

}